IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetBudgetDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetBudgetDetails]
GO
/****** Object:  StoredProcedure [dbo].[GetBudgetDetails]    Script Date: 11/07/2007 13:44:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/************************************************************************************************
* Name:		GetBudgetDetails
* Purpose:	Stored Procedure for Retrieving Budget Details for a specified Fund and Budget Year.
*
* PARAMETERS
* Name				Description
* ------------		-----------------------------
* @domainUserId		Id of user making request
* @fundNumber		Fund number of interest
* @budgetYear		Fiscal year requested: 0 = current year
* @monthEndingDate	Date of latest month close
* @cumulative		Flag to get one year's data or cumulative numbers
* @BudgetEndingDate Budget Ending Date 
* ERRORS USED
* Number		Description
* ------------	-----------------------------
* n/a
*
* Result Set:
*-----------------------------------------------------------------------------------------------
* Name			Description
* ------------	-----------------------------
* n/a
*
* RETURN VALUE
* Name			Description
* ------------	-----------------------------
*
************************************************************************************************/
CREATE PROCEDURE [dbo].[GetBudgetDetails]
(
	@domainUserId		INTEGER,
	@fundNumber			VARCHAR(30),
	@budgetYear			INTEGER		= NULL,
	@monthEndingDate	DATETIME	= NULL,
	@cumulative			INTEGER		= NULL,
	@BudgetEndingDate	DATETIME	= NULL,
	@includeUserOrders	BIT			= NULL
)
AS
BEGIN
	SET NOCOUNT ON

	-- Get the last GL post date
	DECLARE @glPostAsOfDate DATETIME
	EXEC GetRefreshDates 'gl', @glPostAsOfDate OUTPUT

	SET	@budgetYear			= ISNULL(@budgetYear, 0)
	SET	@monthEndingDate	= ISNULL(@monthEndingDate, @glPostAsOfDate)
	SET	@cumulative			= ISNULL(@cumulative, 1)
	SET	@includeUserOrders	= ISNULL(@includeUserOrders, 0)

	-- fiscal funds can't get cumulative budgets
	If ((SELECT TOP 1 AccountingCycleTypeId FROM AgrAgreement_Fct WHERE FundNumber = @fundNumber) = 2) SET @cumulative = 0

	/******************************************************************************
		Constant Declarations and Assigning values to the constants.
	******************************************************************************/
	DECLARE @categoryIndirectCosts		INTEGER
	DECLARE @categoryInvestmentIncome	INTEGER
	DECLARE @categoryOtherIncome		INTEGER
	DECLARE @directCosts				INTEGER
	DECLARE @indirectCosts				INTEGER
	DECLARE @revenue					INTEGER

	SET @categoryIndirectCosts		= 9
	SET @categoryInvestmentIncome	= 10
	SET @categoryOtherIncome		= 14
	SET @directCosts				= 2		
	SET @indirectCosts				= 3
	SET @revenue					= 1

	-- Temp table to hold the Category data.
	IF OBJECT_ID('tempdb..#Category') IS NOT NULL
		DROP TABLE #Category
	CREATE TABLE #Category
	(
		CategoryId			INTEGER,
		SortOrder			INTEGER,
		Category			VARCHAR(256),
		Budget				MONEY,
		Expense				MONEY,
		Encumbrance			MONEY,
		BalanceAvailable	AS (ISNULL(Budget,0) - ISNULL(Expense,0) - ISNULL(Encumbrance,0)),
		AccountId			AS 0,
		AccountNumber		varchar(10)
	)

	-- Table variable to hold the UserOrder Account data.
	CREATE TABLE #UserOrderAccount
	(
		CategoryId			INTEGER,
		SortOrder			INTEGER,
		AccountId			INTEGER,
		AccountNumber		VARCHAR(10),
		AccountDescription	VARCHAR(255),
		Budget				MONEY,
		Expense				MONEY,
		Encumbrance			MONEY,
		BalanceAvailable	AS (-ISNULL(Encumbrance,0))
	)

	-- Temp table to hold the Account Data.
	IF OBJECT_ID('tempdb..#Account') IS NOT NULL
		DROP TABLE #Account
	CREATE TABLE #Account
	(
		CategoryId			INTEGER,
		SortOrder			INTEGER,
		AccountId			INTEGER,
		AccountNumber		VARCHAR(10),
		AccountDescription	VARCHAR(255),
		Budget				MONEY,
		Expense				MONEY,
		Encumbrance			MONEY,
		BalanceAvailable	AS (ISNULL(Budget,0) - ISNULL(Expense,0) - ISNULL(Encumbrance,0))
	)

	-- Temp table to hold the Direct Costs Data.
	IF OBJECT_ID('tempdb..#DirectCosts') IS NOT NULL
		DROP TABLE #DirectCosts
	CREATE TABLE #DirectCosts
	(
		Budget				MONEY,
		Expense				MONEY,
		Encumbrance			MONEY,
		BalanceAvailable	AS (ISNULL(Budget,0) - ISNULL(Expense,0) - ISNULL(Encumbrance,0))
	)

	-- Temp table to hold the Indirect Costs Data.
	IF OBJECT_ID('tempdb..#IndirectCosts') IS NOT NULL
		DROP TABLE #IndirectCosts
	CREATE TABLE #IndirectCosts
	(
		Budget				MONEY,
		Expense				MONEY,
		Encumbrance			MONEY,
		BalanceAvailable	AS (ISNULL(Budget,0) - ISNULL(Expense,0) - ISNULL(Encumbrance,0))
	)

	-- Temp table to hold the Revenue amount.
	IF OBJECT_ID('tempdb..#Revenue') IS NOT NULL
		DROP TABLE #Revenue
	CREATE TABLE #Revenue
	(
		Revenue	MONEY
	)

	-- Temp table to hold the Prior Year Balance
	IF OBJECT_ID('tempdb..#PriorYearBalance') IS NOT NULL
		DROP TABLE #PriorYearBalance
	CREATE TABLE #PriorYearBalance
	(
		PriorYearBalance	MONEY
	)

	IF OBJECT_ID('tempdb..#ProjectHeader') IS NOT NULL
		DROP TABLE #ProjectHeader
	CREATE TABLE #ProjectHeader
	(
		  FundNumber	VARCHAR(8)
		, PIName		VARCHAR(128)
		, ProjectName	VARCHAR(64)
		, SponsorName	VARCHAR(128)
		, SponsorId		VARCHAR(50)
		, BudgetStart	DATETIME
		, BudgetEnd		DATETIME
		, ProjectStart	DATETIME
		, ProjectEnd	DATETIME
		, FundStatus	VARCHAR(30)
		, Institution	VARCHAR(10)
		, ChiefCode		VARCHAR(10)
		, SponsorTypeId VARCHAR(10) 
	)  

	IF @monthendingdate = ''
		SET @monthendingdate = GETDATE()
	/*********************************************************************************************
	If budget Year is not selected, display the Budget Details for the specified Fund Number
		and month ending date.
	Otherwise
		display Budget Details for the selected Fund Number and Budget Year.
	*********************************************************************************************/

	EXEC dbo.GetBudgetDetailResults @domainUserId, @fundNumber, @cumulative, @budgetYear, null, null, @BudgetEndingDate, @includeUserOrders


	/******************************************************************************
		Output Result Set
	******************************************************************************/
	-- a) Category Details
	SELECT
		CategoryId,
		SortOrder,
		AccountId,
		AccountNumber,
		Category,
		ISNULL(Budget,0) AS 'Budget',
		ISNULL(Expense,0) AS 'Expense',
		ISNULL(Encumbrance,0) AS 'Encumbrance',
		ISNULL(BalanceAvailable,0) AS 'BalanceAvailable'
	FROM
		#Category
	ORDER BY
		SortOrder
	-- b) Account Details
	SELECT
		CategoryId,
		SortOrder,
		AccountId,
		AccountNumber + ' - ' + AccountDescription AS 'Category',
		AccountNumber,
		ISNULL(Budget,0) AS 'Budget',
		ISNULL(Expense,0) AS 'Expense',
		ISNULL(Encumbrance,0) AS 'Encumbrance',
		ISNULL(BalanceAvailable,0) AS 'BalanceAvailable'
	FROM
		#Account
	Union
	Select CategoryId, SortOrder, AccountId, AccountNumber, AccountNumber + ' - ' + AccountDescription as 'Category',
			Budget, Expense, Encumbrance, BalanceAvailable From #UserOrderAccount 
	ORDER BY
		SortOrder, AccountNumber
	-- c) Direct Costs Totals
	SELECT
		ISNULL(Budget,0) AS 'Budget',
		ISNULL(Expense,0) AS 'Expense',
		ISNULL(Encumbrance,0) AS 'Encumbrance',
		ISNULL(BalanceAvailable,0) AS 'BalanceAvailable'
	FROM #DirectCosts
	-- d) Indirect Costs Totals
	SELECT
		ISNULL(Budget,0) AS 'Budget',
		ISNULL(Expense,0) AS 'Expense',
		ISNULL(Encumbrance,0) AS 'Encumbrance',
		ISNULL(BalanceAvailable,0) AS 'BalanceAvailable'
	FROM #IndirectCosts
	-- e) Revenue
	SELECT ISNULL(Revenue,0) AS 'Revenue' FROM #Revenue
	-- f) Prior Year Balance
	SELECT ISNULL(PriorYearBalance,0) AS 'PriorYearBalance' FROM #PriorYearBalance

	-- Get the Project Header Information.
	INSERT INTO #ProjectHeader EXEC dbo.GetProjectHeader @fundNumber, NULL, @budgetYear
	SELECT
		FundNumber,
		PIName,
		ProjectName,
		SponsorName,
		SponsorId,
		BudgetStart,
		BudgetEnd,
		ProjectStart,
		ProjectEnd,
		FundStatus
	FROM
		#ProjectHeader

	SET NOCOUNT OFF

END
